package ai;
/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author mammad
 */
public class Jdbc {

    private QueryForm form;

    public Jdbc(QueryForm pForm) {
        form = pForm;
    }

    public boolean checkIncMealCategories(Connection conn) {

        ArrayList<String> queries = new ArrayList<String>();
        form.clearFinalMeals();
        boolean exist = false;

        try {
            Statement mealIngrediens = conn.createStatement();
            //System.out.println(form.getIncMealCategory().size());
            for (int i = 0; i < form.getIncMealCategory().size(); i++) {

                String checkInc = "select mealname from mealcategories where categoryname ='" + form.getIncMealCategory().get(i) + "'";

                queries.add(checkInc);

            }
            String query = "select * from(";
            for (int i = 0; i < queries.size(); i++) {

                query += queries.get(i) + ")";
                if (i != queries.size() - 1) {
                    query += "natural join (";
                }
            }
            ResultSet Result = mealIngrediens.executeQuery(query);
            while (Result.next()) {
                form.addFinalMeals(Result.getString(1));
                exist = true;
            }
            return exist;

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }

    }

    public boolean checkExcMealCategories(Connection conn) {
        ArrayList<String> queries = new ArrayList<String>();
        form.clearFinalMeals();
        boolean exist = false;
        try {
            for (int i = 0; i < form.getExcMealCategory().size(); i++) {
                String checkInc = "SELECT mealname FROM meal m WHERE not ExISTS" +
                        " (SELECT * from mealcategories mi  where mi.mealname=m.mealname" +
                        " and CATEGORYNAME  ='" + form.getExcMealCategory().get(i) + "'" + ")";
                queries.add(checkInc);
            }
            Statement mealIngrediens = conn.createStatement();
            String query = "select * from(";
            for (int i = 0; i < queries.size(); i++) {
                query += queries.get(i) + ")";
                if (i != queries.size() - 1) {
                    query += "natural join (";
                }
            }
            //System.out.println(query);
            ResultSet Result = mealIngrediens.executeQuery(query);
            while (Result.next()) {
                form.addFinalMeals(Result.getString(1));
                exist = true;
            }

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }
        return exist;
    }

    public boolean checkIncMeals(Connection conn) {
        boolean exist = false;
        form.clearFinalMeals(); 
        ArrayList<String> queries = new ArrayList<String>();
   
            
        try {

            Statement mealIngrediens = conn.createStatement();
            for (int i = 0; i < form.getIncMeal().size(); i++) {
            String checkinc = "select * from meal where mealname ='" + form.getIncMeal().get(i) + "'";
                        queries.add(checkinc);
            }
            
             String query = "select * from(";
            for (int i = 0; i < queries.size(); i++) {

                query += queries.get(i) + ")";
                if (i != queries.size() - 1) {
                    query += "union (";
                }
            }

            ResultSet Result = mealIngrediens.executeQuery(query);
            while (Result.next()) {
                exist = true;
                form.addFinalMeals(Result.getString(1));
            }


        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            exist = false;
        }
        return exist;
    }

    public boolean checkExcMeals(Connection conn) {
       boolean exist = false;
        form.clearFinalMeals(); 
System.out.println("jag är här");
   
             String checkExc = "select * from meal where mealname !='";
        try {

            Statement mealIngrediens = conn.createStatement();
            for (int i = 0; i < form.getExcMeal().size(); i++) {
            checkExc+= form.getExcMeal().get(i) + "'";
            
            if (i != form.getExcMeal().size() - 1){
                checkExc +="and mealname != '";
            }

            }
            
System.out.println(checkExc);
        
            ResultSet Result = mealIngrediens.executeQuery(checkExc);
            while (Result.next()) {
                exist = true;
                form.addFinalMeals(Result.getString(1));
            }


        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            exist = false;
        }
        return exist;
    }

    public boolean checkOrIncIngrediens(Connection conn) {
        boolean exist = false;
        form.clearFinalMeals();
        try {
            Statement mealIngrediens = conn.createStatement();
            String checkInc = "SELECT distinct mealname FROM mealingrediens m WHERE ingrediens ='";
            for (int i = 0; i < form.getIncIngrediens().size(); i++) {
                checkInc += form.getIncIngrediens().get(i) + "'";
                if (i != form.getIncIngrediens().size() - 1) {
                    checkInc += " or ingrediens = '";
                }
            }
            ResultSet Result = mealIngrediens.executeQuery(checkInc);
            while (Result.next()) {
                exist = true;
                form.addFinalMeals(Result.getString(1));
            }
        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
        }
        return exist;
    }

    public boolean checkAndIncIngrediens(Connection conn) {
        ArrayList<String> queries = new ArrayList<String>();
        form.clearFinalMeals();
        boolean exist = false;
        try {
            Statement mealIngrediens = conn.createStatement();
            for (int i = 0; i < form.getIncIngrediens().size(); i++) {
                String checkInc = "select mealname from mealingrediens where ingrediens ='" + form.getIncIngrediens().get(i) + "'";
                queries.add(checkInc);

            }
            String query = "select * from(";
            for (int i = 0; i < queries.size(); i++) {

                query += queries.get(i) + ")";
                if (i != queries.size() - 1) {
                    query += "natural join (";
                }
            }
            ResultSet Result = mealIngrediens.executeQuery(query);
            while (Result.next()) {
                form.addFinalMeals(Result.getString(1));
                exist = true;

            }



        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }
        return exist;

    }

    public boolean checkAndExcIngrediens(Connection conn) {
        ArrayList<String> queries = new ArrayList<String>();
        form.clearFinalMeals();
        boolean exist = false;
        try {
            for (int i = 0; i < form.getExcIngrediens().size(); i++) {
                String checkInc = "SELECT mealname FROM meal m WHERE not ExISTS " +
                        "(SELECT * from mealingrediens mi  where mi.mealname=m.mealname" +
                        " and ingrediens ='" + form.getExcIngrediens().get(i) + "'" + ")";
                queries.add(checkInc);

            }
            Statement mealIngrediens = conn.createStatement();
            String query = "select * from(";
            for (int i = 0; i < queries.size(); i++) {
                query += queries.get(i) + ")";
                if (i != queries.size() - 1) {
                    query += "natural join (";
                }

            }

            ResultSet Result = mealIngrediens.executeQuery(query);
            while (Result.next()) {
                form.addFinalMeals(Result.getString(1));
                exist = true;
            }
        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }
        return exist;
    }

    public boolean checkOrExcIngrediens(Connection conn) {
        ArrayList<String> queries = new ArrayList<String>();
        form.clearFinalMeals();
        boolean exist = false;
        try {
            for (int i = 0; i < form.getExcIngrediens().size(); i++) {
                String checkInc = "SELECT mealname FROM meal m WHERE not ExISTS " +
                        "(SELECT * from mealingrediens mi  where mi.mealname=m.mealname" +
                        " and ingrediens ='" + form.getExcIngrediens().get(i) + "'" + ")";
                queries.add(checkInc);
            }
            Statement mealIngrediens = conn.createStatement();
            String query = "select * from(";
            for (int i = 0; i < queries.size(); i++) {

                query += queries.get(i) + ")";
                if (i != queries.size() - 1) {
                    query += "natural join (";
                }
            }
            ResultSet Result = mealIngrediens.executeQuery(query);
            while (Result.next()) {
                form.addFinalMeals(Result.getString(1));
                exist = true;
            }

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
            return false;
        }
        return exist;
    }

    public boolean checkAndIncAndExcIngrediens(Connection conn) {
        String queryInc = "";
        String queryExc = "";
        boolean exist = false;
        form.clearFinalMeals();

        if (!form.getIncIngrediens().isEmpty()) {
            queryInc = "select * from(";
            ArrayList<String> queriesInc = new ArrayList<String>();
            form.clearFinalMeals();

            try {
                Statement mealIngrediens = conn.createStatement();
                for (int i = 0; i < form.getIncIngrediens().size(); i++) {
                    String checkInc = "select mealname from mealingrediens where ingrediens ='" + form.getIncIngrediens().get(i) + "'";
                    queriesInc.add(checkInc);
                }
                for (int i = 0; i < queriesInc.size(); i++) {
                    queryInc += queriesInc.get(i) + ")";
                    if (i != queriesInc.size() - 1) {
                        queryInc += "natural join (";
                    }
                }
                ResultSet Result = mealIngrediens.executeQuery(queryInc);
                while (Result.next()) {
                    form.addFinalMeals(Result.getString(1));
                    exist = true;
                }
            } catch (SQLException e) {
                System.out.println("error...");
                e.printStackTrace();
                return false;
            }
        } else if (!form.getExcIngrediens().isEmpty()) {



            ArrayList<String> queriesExc = new ArrayList<String>();
            form.clearFinalMeals();
            boolean existExc = false;
            try {
                for (int i = 0; i < form.getExcIngrediens().size(); i++) {
                    String checkInc = "SELECT mealname FROM meal m WHERE not ExISTS (SELECT * from mealingrediens mi " +
                            " where mi.mealname=m.mealname and ingrediens ='" + form.getExcIngrediens().get(i) + "'" + ")";
                    queriesExc.add(checkInc);
                }
                Statement mealIngrediens = conn.createStatement();
                queryExc = "select * from (";
                for (int i = 0; i < queriesExc.size(); i++) {

                    queryExc += queriesExc.get(i) + ")";
                    if (i != queriesExc.size() - 1) {
                        queryExc += "natural join (";
                    }
                }
                ResultSet Result = mealIngrediens.executeQuery(queryExc);
                while (Result.next()) {
                    form.addFinalMeals(Result.getString(1));
                    exist = true;
                }


            } catch (SQLException e) {
                System.out.println("error...");
                e.printStackTrace();
                return false;
            }
        } else if (!form.getExcIngrediens().isEmpty() && !form.getExcIngrediens().isEmpty()) {
            try {
                Statement mealIngrediens = conn.createStatement();
                String queryTot = queryInc + "natural join (" + queryExc + ")";
                ResultSet ResultTotal = mealIngrediens.executeQuery(queryTot);
                while (ResultTotal.next()) {
                    form.addFinalMeals(ResultTotal.getString(1));
                    exist = true;
                }
            } catch (SQLException e) {
                System.out.println("error...");
                e.printStackTrace();
                return false;
            }
        }
        return exist;
    }

    public void getDatabaseIngr(Connection conn) {
        try {
            Statement mealIngrediens = conn.createStatement();
            String checkExc = "select distinct(ingrediens) from mealingrediens";
            ResultSet Result = mealIngrediens.executeQuery(checkExc);
            while (Result.next()) {
                form.addDatabaseIngr(Result.getString(1));
            }

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
        }

    }

    public void getDatabaseMealCat(Connection conn) {

        try {
            Statement mealIngrediens = conn.createStatement();
            String checkExc = "select distinct categoryname from mealcategories";
            ResultSet Result = mealIngrediens.executeQuery(checkExc);
            while (Result.next()) {

                form.addDatabaseMealcat(Result.getString(1));
            }

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
        }

    }
    public void getDatabaseMeals(Connection conn) {

        try {
            Statement mealIngrediens = conn.createStatement();
            String checkExc = "select mealname from meal";
            ResultSet Result = mealIngrediens.executeQuery(checkExc);
            while (Result.next()) {

                form.addDatabaseMeals(Result.getString(1));
            }

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();
        }

    }

    public void finalMeal(Connection conn) {

        try {
            Statement fmeal = conn.createStatement();

            for (int i = 0; i <
                    form.getFinalMeals().size(); i++) {
                String check1 = "select mi.mealname,TIMETOPREPARE , INGREDIENS,AMOUNTINGREDIENS,UNIT,WAYTOCOOCK,EQUIPMENTDEMANDING from meal m,mealingrediens" +
                        " mi, waytocoock w where m.mealname = mi.mealname and" +
                        " m.mealname = w.mealname and m.mealname ='" + form.getFinalMeals().get(i) + "'";

                String check2 = "select * from meal where mealname = '" + form.getFinalMeals().get(i) + "'";
                String check3 = "select * from WAYTOCOOCK where mealname = '" + form.getFinalMeals().get(i) + "'";
                ResultSet Result2 = fmeal.executeQuery(check2);
                Result2.next();
                System.out.println("Mealname:  " + Result2.getString(1) + "\t" + "Time to prepare   " + Result2.getString(2) + "\n");
                //System.out.println(check1);
                ResultSet Result1 = fmeal.executeQuery(check1);
                System.out.println("Ingredients\tAmount\t\tUnit" + "\n");
                while (Result1.next()) {

                    if (Result1.getString(3).length() >= 8) { // 8 seems to work best...
                        System.out.println(Result1.getString(3) + "\t" + Result1.getString(4) + "\t\t" + Result1.getString(5) + "\n");
                    } else {
                        System.out.println(Result1.getString(3) + "\t\t" + Result1.getString(4) + "\t\t" + Result1.getString(5) + "\n");
                    }

                }

                ResultSet Result3 = fmeal.executeQuery(check3);
                Result3.next();
                System.out.println("Way to cook: \n");

                System.out.println(Result3.getString(2) + "\n");
            }

        } catch (SQLException e) {
            System.out.println("error...");
            e.printStackTrace();

        }
    }
    }
